package com.lvtulife.common.util.db;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.*;
import java.util.List;

/**
 * 用于JDBC操作数据库的共通类
 * 
 * @author Bianjing
 * @version 1.0.0 2011-9-5
 */
public class CommonSql {
	private static final Logger logger = LoggerFactory.getLogger(CommonSql.class);
	
	/** 数据源 */
	private DataSource dataSource;

	/** 数据库连接对象 */
	private Connection connection;

	/** 数据库操作对象 */
	private PreparedStatement ps;

	/** 数据库操作对象 */
	private Statement statement;

	/** 返回的数据结果集对象 */
	private ResultSet rs;

	/** 是否自动提交事务，默认为true，如果该值为false则需要手动提交事务 */
	private boolean autoCommit = true;

	/** 数据库连接是否已经打开 */
	private boolean openConnection;

	/** JNDI名称 */
	private String jndiName;

	/** 数据库驱动 */
	private String driver;

	/** 数据库访问地址 */
	private String url;

	/** 用户名 */
	private String user;

	/** 密码 */
	private String pwd;

	public CommonSql() {

	}

	public CommonSql(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public CommonSql(String jndiName) {
		this.jndiName = jndiName;
	}

	public CommonSql(String driver, String url, String user, String pwd) {
		this.driver = driver;
		this.url = url;
		this.user = user;
		this.pwd = pwd;
	}

	/**
	 * 打开数据库连接并创建数据库连接对象<br/>
	 * 支持通过ICO注入数据源、数据库驱动、数据库驱动、JNDI名称、数据库访问地址和用户名、密码
	 * 
	 * @return boolean true:连接成功，false:连接失败
	 */
	public boolean openConnection() {
		/**
		 * 通过数据源来获取数据库连接对象
		 */
		if (dataSource != null) {
			try {
				connection = dataSource.getConnection();
				// 数据库连接已经打开
				openConnection = true;
			} catch (SQLException e) {
				closeAll();
				// 所有的"logger.info"都可以替换为"logger.error"
				logger.info("从数据源获取数据库连接失败！");
				throw new RuntimeException(e);
			}

			return openConnection;
		}
		/**
		 * 通过JNDI来获取数据库连接对象
		 */
		if (jndiName != null) {
			try {
				Context initContext = new InitialContext();
				dataSource = (DataSource) initContext.lookup(jndiName);
				connection = dataSource.getConnection();
				// 数据库连接已经打开
				openConnection = true;
			} catch (Exception e) {
				closeAll();
				logger.info("从JNDI获取数据库连接失败！");
				throw new RuntimeException(e);
			}

			return openConnection;
		}
		/**
		 * 通过数据库驱动、数据库访问地址、用户名、密码来获取数据库连接对象
		 */
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, user, pwd);
			// 数据库连接已经打开
			openConnection = true;
		} catch (Exception e) {
			closeAll();
			logger.info("数据库连接失败！");
			throw new RuntimeException(e);
		}

		return openConnection;
	}

	/**
	 * 执行数据库的更新操作
	 * 
	 * @param sql
	 *            要执行的SQL语句
	 * @return boolean true:执行成功，false:执行失败
	 */
	public boolean execUpdate(String sql, Object... args) {
		boolean isPassed = false;
		// 判断连接数据库是否成功
		if (openConnection) {
			try {
				ps = connection.prepareStatement(sql);
				// 设置参数
				if (args != null && args.length > 0) {
					for (int i = 0; i < args.length; i++) {
						ps.setObject(i + 1, args[i]);
					}
				}
				ps.executeUpdate();

				isPassed = true;
			} catch (SQLException e) {
				try {
					if (autoCommit) {
						connection.rollback();
					}
				} catch (SQLException e1) {
					throw new RuntimeException(e1);
				}
				logger.info("SQL:" + sql);
				e.printStackTrace();
				throw new RuntimeException(e);
			} finally {
				if (autoCommit) {
					closeAll();
				}
			}
		} else {
			logger.info("数据库连接对象没有打开！");
		}

		return isPassed;
	}

	/**
	 * 执行数据库的更新操作
	 * 
	 * @param sql
	 *            要执行的SQL语句
	 * @return boolean true:执行成功，false:执行失败
	 */
	public boolean execUpdate(String sql, List<?> args) {
		return execUpdate(sql, args.toArray());
	}

	/**
	 * 执行批量更新数据库操作
	 * 
	 * @param sql
	 *            要执行的SQL语句的字符串数组
	 * @return boolean true:执行成功，false:执行失败
	 */
	public boolean execUpdate(Object[] sql) {
		boolean flag = false;
		// 判断连接数据库是否成功
		if (openConnection) {
			try {
				statement = connection.createStatement();
				for (int i = 0; i < sql.length; i++) {
					statement.addBatch((String) sql[i]);
				}
				statement.executeBatch();

				flag = true;
			} catch (SQLException e) {
				try {
					if (autoCommit) {
						connection.rollback();
					}
				} catch (SQLException e1) {
					throw new RuntimeException(e1);
				}
				for (int i = 0; i < sql.length; i++) {
					logger.info("SQL " + (i + 1) + ":" + sql[i]);
				}
				throw new RuntimeException(e);
			} finally {
				if (autoCommit) {
					closeAll();
				}
			}
		} else {
			logger.info("数据库连接对象没有打开！");
		}

		return flag;
	}

	/**
	 * 执行批量更新数据库操作
	 * 
	 * @param sql
	 *            要执行的SQL语句的集合
	 * @return boolean true:执行成功，false:执行失败
	 */
	public boolean execUpdate(List<?> sql) {
		return execUpdate(sql.toArray());
	}

	/**
	 * 执行数据库查询操作
	 * 
	 * @param sql
	 *            要执行的SQL语句
	 * @param args
	 *            查询参数列表
	 * @return ResultSet 返回查询的结果集对象
	 */
	public ResultSet execQuery(String sql, Object... args) {
		rs = null;
		// 判断连接数据库是否成功
		if (openConnection) {
			try {
				ps = connection.prepareStatement(sql);
				// 设置参数
				if (args != null && args.length > 0) {
					for (int i = 0; i < args.length; i++) {
						ps.setObject(i + 1, args[i]);
					}
				}

				rs = ps.executeQuery();
			} catch (SQLException e) {
				if (autoCommit) {
					closeAll();
				}
				logger.info("SQL:" + sql);
				throw new RuntimeException(e);
			}
		} else {
			logger.info("数据库连接对象没有打开！");
		}

		return rs;
	}

	/**
	 * 执行数据库查询操作
	 * 
	 * @param sql
	 *            要执行的SQL语句
	 * @param args
	 *            查询参数列表
	 * @return ResultSet 返回查询的结果集对象
	 */
	public ResultSet execQuery(String sql, List<?> args) {
		return execQuery(sql, args.toArray());
	}

	/**
	 * 根据标准SQL查询数据库，返回一个int值
	 * 
	 * @param sql
	 *            要执行的SQL语句
	 * @param args
	 *            查询参数列表
	 * @return int值，如果出错则返回-1
	 */
	public int findForInt(String sql, Object... args) {
		ResultSet rs = execQuery(sql, args);
		int count = -1;
		try {
			if (rs != null && rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (autoCommit) {
				closeAll();
			}
		}

		return count;
	}

	/**
	 * 根据标准SQL查询数据库，返回一个int值
	 * 
	 * @param sql
	 *            要执行的SQL语句
	 * @param args
	 *            查询参数列表
	 * @return int值，如果出错则返回-1
	 */
	public int findForInt(String sql, List<?> args) {
		return findForInt(sql, args.toArray());
	}

	/**
	 * 关闭所有数据库连接对象
	 */
	public void closeAll() {
		if (rs != null || ps != null || statement != null || connection != null) {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				throw new RuntimeException(e);
			} finally {
				rs = null;
				if (ps != null || statement != null || connection != null) {
					try {
						if (ps != null && !ps.isClosed()) {
							ps.close();
						}
					} catch (SQLException e) {
						throw new RuntimeException(e);
					} finally {
						ps = null;
						if (statement != null || connection != null) {
							try {
								if (statement != null && !statement.isClosed()) {
									statement.close();
								}
							} catch (SQLException e) {
								throw new RuntimeException(e);
							} finally {
								statement = null;
								try {
									if (connection != null && !connection.isClosed()) {
										connection.close();
									}
								} catch (SQLException e) {
									throw new RuntimeException(e);
								} finally {
									connection = null;
								}
							}
						}
					}
				}
			}
		}
	}

	/**
	 * 提交事务并关闭数据库连接
	 */
	public void commit() {
		try {
			if (!autoCommit) {
				connection.commit();
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			autoCommit = true;
			closeAll();
		}
	}

	/**
	 * 回滚事务并关闭数据库连接
	 */
	public void rollback() {
		try {
			if (!autoCommit) {
				connection.rollback();
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			autoCommit = true;
			closeAll();
		}
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public String getDriver() {
		return driver;
	}

	public void setDriver(String driver) {
		this.driver = driver;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public boolean getAutoCommit() {
		return autoCommit;
	}

	public void setAutoCommit(boolean autoCommit) {
		try {
			connection.setAutoCommit(autoCommit);
		} catch (SQLException e) {
			closeAll();
			throw new RuntimeException(e);
		}
		this.autoCommit = autoCommit;
	}

	public boolean getOpenConnection() {
		return openConnection;
	}

	public String getJndiName() {
		return jndiName;
	}

	public void setJndiName(String jndiName) {
		this.jndiName = jndiName;
	}

	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}

	public Connection getConnection() {
		return connection;
	}

	/**
	 * 测试数据库连接是否成功
	 * 
	 * @param args
	 * @throws SQLException
	 */
	public static void main(String[] args) throws SQLException {
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=demo";
		String user = "sa";
		String pwd = "sa";
		
		CommonSql commonSql = new CommonSql(driver, url, user, pwd);
		if (commonSql.openConnection()) {
			logger.info("数据库连接成功！");
			DatabaseMetaData dbMetaData = commonSql.getConnection().getMetaData();
			System.out.print("当前连接的数据库是:" + dbMetaData.getDatabaseProductName());
			logger.info(" " + dbMetaData.getDatabaseProductVersion());
		} else {
			logger.info("数据库连接失败！");
		}

		commonSql.closeAll();
	}
}
